<?php /**
* vim: tabstop=4
*
* @license		http://www.gnu.org/licenses/gpl.html GPL Version 3
* @author		Ian Moore <imooreyahoo@gmail.com>
* @copyright	Copyright (c) 2011 Ian Moore
* @version		$Id: mysql.inc 92 2011-12-28 23:09:12Z imooreyahoo@gmail.com $
*
* This file is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* any later version.
*
* This file is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this file. If not, see <http://www.gnu.org/licenses/>.
*
*/

require_once("openmediavault/object.inc");
require_once("openmediavault/error.inc");
require_once("openmediavault/util.inc");
require_once("openmediavault/rpc.inc");
require_once("openmediavault/notify.inc");
require_once("openmediavault/monit.inc");

class MySQLRpc extends OMVRpc {

	const xpathRoot = '//services/mysql';

	const dbValidRegex = "/^[a-z#][a-z0-9_# \\\.\\\-%\\\\\\\]*$/i";

	/* System users that should not be manipulated */
	static $system_users = array('root','debian-sys-maint');

	/* The following privilege lists where copied / pasted
	 * directly from phpMyAdmin code
	 */
	static $global_grants = array(
		array('Select_priv', 'SELECT'),
		array('Insert_priv', 'INSERT'),
		array('Update_priv', 'UPDATE'),
		array('Delete_priv', 'DELETE'),
		array('Create_priv', 'CREATE'),
		array('Drop_priv', 'DROP'),
		array('Reload_priv', 'RELOAD'),
		array('Shutdown_priv', 'SHUTDOWN'),
		array('Process_priv', 'PROCESS'),
		array('File_priv', 'FILE'),
		array('References_priv', 'REFERENCES'),
		array('Index_priv', 'INDEX'),
		array('Alter_priv', 'ALTER'),
		array('Show_db_priv', 'SHOW DATABASES'),
		array('Super_priv', 'SUPER'),
		array('Create_tmp_table_priv', 'CREATE TEMPORARY TABLES'),
		array('Lock_tables_priv', 'LOCK TABLES'),
		array('Create_view_priv', 'CREATE VIEW'),
		array('Event_priv', 'EVENT'),
		array('Trigger_priv', 'TRIGGER'),
		array('Show_view_priv', 'SHOW VIEW'),
		array('Create_routine_priv', 'CREATE ROUTINE'),
		array('Alter_routine_priv', 'ALTER ROUTINE'),
		array('Create_user_priv', 'CREATE USER'),
		array('Execute_priv', 'EXECUTE'),
		array('Repl_slave_priv', 'REPLICATION SLAVE'),
		array('Repl_client_priv', 'REPLICATION CLIENT')
	);

	static $db_grants = array(
		array('Select_priv', 'SELECT'),
		array('Insert_priv', 'INSERT'),
		array('Update_priv', 'UPDATE'),
		array('Delete_priv', 'DELETE'),
		array('Create_priv', 'CREATE'),
		array('Execute_priv', 'EXECUTE'),
		array('Drop_priv', 'DROP'),
		array('References_priv', 'REFERENCES'),
		array('Index_priv', 'INDEX'),
		array('Alter_priv', 'ALTER'),
		array('Create_tmp_table_priv', 'CREATE TEMPORARY TABLES'),
		array('Lock_tables_priv', 'LOCK TABLES'),
		array('Create_view_priv', 'CREATE VIEW'),
		array('Event_priv', 'EVENT'),
		array('Trigger_priv', 'TRIGGER'),
		array('Show_view_priv', 'SHOW VIEW'),
		array('Create_routine_priv', 'CREATE ROUTINE'),
		array('Alter_routine_priv', 'ALTER ROUTINE')
	);

	/* Database connection reference */
	var $_db = null;

	
	public function __construct() {

		$this->methodSchemata = array(

			"set" => array('{
				"type":"object",
				"properties":{
					"enable":{"type":"boolean"},
					"enable-networking":{"type":"boolean","optional":true},
					"moverootdata":{"type":"boolean","optional":true},
					"enable-phpmyadmin":{"type":"boolean","optional":true},
					"force":{"type":"boolean","optional":true},
					"extraoptions":{"type":"string","optional":true},
					"bindaddress":{"type":"string","optional":true,"format":"regex",
						"pattern":"/^[0-9]{1,3}\\\.[0-9]{1,3}\\\.[0-9]{1,3}\\\.[0-9]{1,3}/"},
					"port":{"type":"integer", "optional":true},
					"password":{"type":"string","optional":true},
					"curpassword":{"type":"string","optional":true},
					"data-root":{"type":"string","format":"regex","pattern":'.
						'"/^(\\\/[a-z0-9_\\\/\\\-]+|)$/i","optional":true},
					"mntentref":{"type":"string","format":"regex","pattern":'.
  						'"\/^([a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}|none)$\/i","optional":true}
				}
			}'),
            "getDatabases" => array(
                '{"type":"integer"}', // start
                '{"type":"integer"}', // count
                '{'.$GLOBALS['OMV_JSONSCHEMA_SORTFIELD'].'}', // sortField
                '{'.$GLOBALS['OMV_JSONSCHEMA_SORTDIR'].'}' // sortDir
			),
			"getPrivilege" => array(
				'{"type":"string"}',
				'{"type":"string"}',
				'{"type":"string","optional":true}',
				'{"type":"boolean","optional":true}'
			),

            "getPrivileges" => array(
                '{"type":"integer"}', // start
                '{"type":"integer"}', // count
                '{'.$GLOBALS['OMV_JSONSCHEMA_SORTFIELD'].'}', // sortField
                '{'.$GLOBALS['OMV_JSONSCHEMA_SORTDIR'].'}', // sortDir
				'{"type":"string","optional":true}'
			),


            "setDatabase" => array('{
                "type":"object",
                "properties":{
                    "uuid":{"type":"string"},
                    "name":{"type":"string","format":"regex",
						"pattern":"'.self::dbValidRegex.'"},
                    "collation":{"type":"string","optional":true},
                    "exists":{"type":"string","enum":["0","1"]}
				}
			}'),

            "removeDatabase" => array(
                '{"type":"string","format":"regex",
					"pattern":"'.self::dbValidRegex.'"}'
			)

		);
	}

	/**
	 * Safe config getting / setting
	 */
	public function __call($name, $args) {

		// Configuration methods
		if(substr($name,0,6) == 'config') {
				
			// Correct method name
			$name = substr($name,6);
			$name[0] = strtolower($name[0]);
				
			global $xmlConfig;
			$object = call_user_func_array(array($xmlConfig,$name),$args);
			switch($name) {
				case 'delete':
					if($object === false)
						throw new OMVException(OMVErrorMsg::E_CONFIG_OBJECT_NOT_FOUND, $args[0]);
					break;
				case 'save':
					if($object === false)
						throw new OMVException(OMVErrorMsg::E_CONFIG_SAVE_FAILED, $xmlConfig->getError());
					break;
				case 'set':
				case 'replace':
					if($object === false)
						throw new OMVException(OMVErrorMsg::E_CONFIG_SET_OBJECT_FAILED);
					break;
				default:
					if(is_null($object))
						throw new OMVException(OMVErrorMsg::E_CONFIG_GET_OBJECT_FAILED, $args[0]);
			}
				
			return $object;
				
		}
		throw new Exception("Method ".__CLASS__."::".$name." does not exist.");
	}

	/**
	 * Verify that the current user is an admin, and validate method args
	 */
	function _validate($mname='',$args=array()) {

		// Check permissions
		$this->validateSession();
		if (!$this->hasRole(OMV_ROLE_ADMINISTRATOR)) {
			throw new OMVException(OMVErrorMsg::E_RPC_SERVICE_INVALID_PERMISSION);
		}
		$this->commitSession();
			
		// Check incoming data
		if($mname) $this->validateParams($mname, $args);
	}

	/**
	 * Remove phpMyAdmin package using dpkg
	 */
	function removeMyAdmin() {

		$this->_validate();

		$cmd = sprintf("sudo apt-get --yes --auto-remove --purge remove " .
          "%s 2>&1", "phpmyadmin");
		OMVUtil::exec($cmd, $output, $result);
		if ($result !== 0) {
			throw new OMVException(OMVErrorMsg::E_EXEC_FAILED,
			$cmd, implode("\n", $output));
		}

		// Set to disable phpmyadmin?
		$enabled = boolval($this->configGet(self::xpathRoot."/enable-phpmyadmin"));

		// nothing more to do
		if(!$enabled) return;

		$this->configReplace(self::xpathRoot."/enable-phpmyadmin", "0");
		$this->configSave();

		// This will disable the apache site 
		$cmd = "sudo omv-mkconf mysql 2>&1";
		OMVUtil::exec($cmd, $output, $result);
		if ($result !== 0) {
			throw new OMVException(OMVErrorMsg::E_EXEC_FAILED, $cmd, implode("\n", $output));
		}

		// Restart apache so disabling of phpmyadmin takes affect
		$monit = new OMVMonit();
		$monit->restart("apache2", TRUE);

	}

	/**
	 * 
	 * Install phpMyAdmin using apt-get
	 * @throws OMVException
	 * @return number process id of spawned apt-get process
	 */
	function installMyAdmin() {

		// Check permissions
		$this->validateSession();
		if (!$this->hasRole(OMV_ROLE_ADMINISTRATOR)) {
			throw new OMVException(OMVErrorMsg::E_RPC_SERVICE_INVALID_PERMISSION);
		}

		// Install package
		$outputFile = tempnam(sys_get_temp_dir(), "aptgetinstall");
		$cmd = sprintf("sudo /usr/bin/env DEBIAN_FRONTEND=noninteractive apt-get --yes --force-yes --fix-missing " .
			"--allow-unauthenticated --reinstall install %s >%s 2>&1 & echo $!",
			"phpmyadmin", $outputFile);
		OMVUtil::exec($cmd, $output, $res);
		if ($res !== 0) {
			throw new OMVException(OMVErrorMsg::E_EXEC_FAILED,
			$cmd, implode("\n", $output));
		}
		
		// Remember pid and content filename in session
		$pid = intval($output[0]);
		$_SESSION['execjob'][$pid] = array(
            "pid" => $pid,
            "outputFile" => $outputFile
		);
		return $pid;
	}

	/**
	 * Get all configuration data for service.
	 * @return array configuration data
	 */
	function get() {

		// Validation
		$this->_validate();
		
		//Get configuration object
		$object = $this->configGet(self::xpathRoot);

		// Modify result data
		$object['enable-phpmyadmin'] = boolval($object['enable-phpmyadmin']);

		// Merge with actual mysql configuration and status
		$econf = $this->_getConfig();

		// status
		$initd = new OMVSysVInitScript("mysql");
		$object['enable'] = ($initd->status() == 0);


		// Other vals
		$object['port'] = (empty($econf['port']) ? 3306 : $econf['port']);
		$object['bindaddress'] = (empty($econf['bind-address']) ? '0.0.0.0' : $econf['bind-address']);
		$object['enable-networking'] = empty($econf['skip-networking']);


		if(!empty($object['password'])) {
			unset($object['password']);
			$object['warnnopassword'] = 0;
		} else {
			$object['warnnopassword'] = 1;
		}

		// check for phpMyAdmin
		exec("sudo dpkg -s phpmyadmin | grep '^Status: '", $out);
		$object['phpmyadmininstalled'] = (strpos(implode('',$out),' installed') !== false);
	
		// Defaults to "none"	
		if(empty($object['mntentref']))
			$object['mntentref'] = 'none';

		// If mysql_connect doesn't exist, assume apache
		// needs to be restarted
		if(!function_exists('mysql_connect')) {
			$monit = new OMVMonit();
			$monit->restart("apache2", TRUE);
		}
		
		return $object;
	}

	/**
	 * Set configuration data for service.
	 * @param $data array configuration data
	 */
	function set($data) {

		// Validation
		$this->_validate(__METHOD__,func_get_args());

        //Get existing configuration object
        $econf = $this->configGet(self::xpathRoot);

		// Merge unset values in case they where
		// not passed because form fields were disabled
		foreach($econf as $k=>$v) {
			if(!isset($data[$k])) $data[$k] = $v;
		}

		/*
		 * If mntentref being changed? This will change
		 * the data root
		 */
		if($data['mntentref'] != $econf['mntentref']) {

			// Calcualte data root
			if($data['mntentref'] == 'none') {

				// Use the default
				$data['data-root'] = '';

			// Use calcuated root
			} else {

				// Set data root accordingly
	        	$pdir = sprintf("//system/fstab/mntent[uuid='%s']/dir", $data['mntentref']);
		        $pdir = $this->configGet($pdir);
		        $data['data-root'] = "{$pdir}/mysql-data";
			}

           	// data-root will be changed, check for existing dbs
			if($econf['data-root'] != $data['data-root']) {
            
    	    	// Get default data dir
				$dataroot = $econf['data-root'];
				if(!$dataroot) {
	    	    	exec("sudo grep '^datadir\s' /etc/mysql/my.cnf /etc/mysql/conf.d/* ".
		            	"| grep -v openmediavault-mysql.cnf | awk '{ print \$NF }' | tail -1", $myconf);

					$dataroot = $myconf[0];
				
				}

				// Find all directories here that are not named "mysql"
				// which is the mysql system DB that exists in every mysql isntall
				exec("sudo find {$dataroot} -maxdepth 1 -type d -not -name mysql",$out);
            	if(count($out) && $out[0] == $dataroot) array_shift($out);

				// Directory found. 
            	if(count($out)) {
                	throw new Exception("Refusing to change MySQL data root while ".
                    	"databases exist in {$dataroot}. This may result in ".
                    	"loss of data.");
            	}

			}

		// Use existing configuration value for data-root
		} else {
			$data['data-root'] = $econf['data-root'];
		}

		// If we're just setting the config object to the current
		// password then just make sure we set the config
		if(!empty($data['curpassword']) && empty($econf['password']) && empty($data['password'])) {
			$data['password'] = $econf['password'] = $data['curpassword'];
		}

		/**
		 * Set configuration values
		 */
		$object = array(
			"enable" => array_boolval($data, 'enable'),
			"enable-networking" => array_boolval($data, 'enable-networking'),
			"enable-phpmyadmin" => (
				array_boolval($data, 'enable-phpmyadmin') && array_boolval($data, 'enable')
			),
			"extraoptions" => (!empty($data['extraoptions']) ? $data['extraoptions'] : ''),
			"port" => intval($data['port']),
			"bindaddress" => $data['bindaddress'],
			"password" => (
				(!empty($data['curpassword']) || !empty($data['password']) || $data['force']) ?
					$data['password'] : $econf['password']
			),
			"data-root" => $data['data-root'],
			"mntentref" => $data['mntentref']
			
		);

		// Set configuration object
		$this->configReplace(self::xpathRoot, $object);
		$this->configSave();

		// Notify of general configuration changes
		$dispatcher = &OMVNotifyDispatcher::getInstance();
		$dispatcher->notify(OMV_NOTIFY_MODIFY,
			"org.openmediavault.services.mysql", $object, $econf);
		
		// Set or change root password
		if($object['enable'] &&
			(
				// Different passwords?
				($econf['password'] != $object['password']) ||
				
				// "force" is selected and password was sent
			 	(array_boolval($data,'force') && strlen(trim($object['password'])) )
			 )
		) {

			try {

				if(empty($data['curpassword'])) $data['curpassword'] = '';
				
				// "Normal" MySQL root password change
				if(!array_boolval($data,'force')) {

					$this->connect($data['curpassword']);

					$chpw = sprintf("UPDATE mysql.user SET Password=PASSWORD('%s') WHERE User='root';",
					$this->escape($data['password']));

					$this->query($chpw);
					$this->query("FLUSH PRIVILEGES;");

				// Forced MySQL root password change
				} else {

					$cmd = "sudo omv-mkconf mysql-password 2>&1";
					OMVUtil::exec($cmd, $output, $result);
					if ($result !== 0) {
						throw new OMVException(OMVErrorMsg::E_EXEC_FAILED, $cmd, implode("\n", $output));
					}
				}
			
			} catch (Exception $e) {

				// Failed to change passowrd, change config back to old value
				$object['password'] = $econf['password'];
				
				// Set configuration object
				$this->configReplace(self::xpathRoot, $object);
				$this->configSave();

				// re-raise error
				throw new Exception($e->getMessage());

			}
		}

	}

	/**
	 * Connect to MySQL
	 * @param string $pw optional password to use rather than defalt from config
	 * @throws Exception
	 */
	public function connect($pw=false) {
		if($this->_db !== null) return;
		$this->_db = mysql_connect(null, "root", ($pw === false ? $this->configGet(self::xpathRoot.'/password') : $pw));
		if(!$this->_db) {
			throw new Exception("Error connecting to MySQL: ". mysql_error());
		}
	}

	/**
	 * Escape a string for use in SQL query using mysql_real_escape_string
	 * @param string $q string to be escaped
	 * @returns string escaped string
	 */
	public function escape($q) {
		if($this->_db === null) $this->connect();
		return mysql_real_escape_string($q,$this->_db);
	}

	/**
	 * Escape a database name
	 * @param string $db database name
	 * @param boolean $wildcards allow wildcards _ and %
	 * @return string the escaped database name
	 */
	public function db_escape($db, $wildcards=false) {

		if($wildcards)
			return str_replace('`','',$db);

		return str_replace(array('`','_'),array('','\_'), $db);
	}

	/**
	 * Send SQL statement to MySQL
	 * @param string $q SQL statement to send
	 * @param boolean $kv return results in key / value paris
	 * @throws Exception
	 */
	public function query($q, $kv = false) {

		if($this->_db === null) {
			$this->connect();
		}

		$resultset = mysql_query($q,$this->_db);

		if($resultset === false) {
			throw new Exception("MySQL Error: " . mysql_error($this->_db) .
				(stripos($q,"password") < 0 ? ': '. $q : ''));
		}

		// Just running a command, return result
		if($resultset === true)  {
			return $resultset;
		}

		$retval = array();

		if($kv) {
			while($row = mysql_fetch_array($resultset))
				$retval[$row[0]] = $row[1];
		} else {
			while($row = mysql_fetch_assoc($resultset))
				$retval[] = $row;
		}

		mysql_free_result($resultset);

		return $retval;

	}

	/**
	 * Close MySQL connection when this instance is no longer in use
	 */
	public function __destruct() {

		if($this->_db !== null)
		mysql_close($this->_db);

	}

	/**
	 * Return a list of collations and character sets supported
	 * by this MySQL installation
	 * @returns array array of collation names, descriptions, and charsets
	 */
	public function getCollations() {

		$sets = $this->query("SHOW CHARACTER SET;");
		$colls = $this->query("SHOW COLLATION;");

		$charsetDescs = array();
		foreach($sets as $s) {
			$charsetDescs[$s['Charset']] = $s['Description'];
		}

		$objects = array();
		foreach($colls as $k=>$v) {
			$objects[$k] = array(
				'charset' => $v['Charset'],
				'collation' => $v['Collation'],
				'description' => $charsetDescs[$v['Charset']] ." ({$v['Collation']})"
			);
		}

		$objects[] = array(
			'charset' => '',
			'collation' => '',
			'description' => 'Default'
		);

		usort($objects, function($a,$b) {
			if($a['charset'] == $b['charset']) return strnatcasecmp($a['collation'],$b['collation']);
			return strnatcasecmp($a['charset'],$b['charset']);

		});

		return $objects;
	}

	/**
	 * 
	 * Create database
	 * @param array $data method input data
	 */
	public function setDatabase($data) {

		$this->_validate(__METHOD__, func_get_args());

		// Just update collation
		if(boolval($data['exists']))  {

			if(!empty($data['collation'])) {
				$uuid = $this->db_escape($data['uuid'], true);
				$coll = $this->escape($data['collation']);
				$this->query("ALTER DATABASE `{$uuid}` COLLATE '{$coll}'");
			}

			// Create new database
		} else {

			$name = $this->db_escape($data['name'], true);
			$sql = "CREATE DATABASE `{$name}`";

			if(!empty($data['collation'])) {
				$coll = $this->escape($data['collation']);
				$sql .= " COLLATE = '{$coll}'";
			}
			$this->query($sql);
		}


	}

	/**
	 * 
	 * Drop database
	 * @param string $name name of database to drop
	 */
	public function removeDatabase($name) {

		$this->_validate(__METHOD__, func_get_args());
		$this->connect();
		$this->query("DROP DATABASE `". $this->db_escape($name, true) ."`;");
	}

	/**
	 * 
	 * Return database collation configuration
	 * @param string $name name of database
	 * @retuns array containing database name and collation setting
	 */
	public function getDatabase($name) {


		if($name) {
			$this->connect();
			mysql_select_db($name,$this->_db);
			$coll = $this->query("show variables like 'collation_database';");
			$coll = $coll[0]['Value'];
		} else {
			$coll = '';
		}
		return array('name'=>$name,'collation'=>$coll);
	}
	
	/**
	 * 
	 * Return the list of databases
	 * @param integer $start
	 * @param integer $count
	 * @param unkown $sortField
	 * @param unknown $sortDir
	 * @returns array list of databases
	 */
	public function getDatabases($start, $count, $sortField, $sortDir) {

		$this->_validate(__METHOD__,func_get_args());

		$sizes = $this->query('SELECT TABLE_SCHEMA "Database", '.
			'sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)) AS size '.
			'FROM INFORMATION_SCHEMA.TABLES Group By TABLE_SCHEMA;', true);

		$objects = $this->query('show databases');
		foreach($objects as $k=>$v) {
			if(empty($sizes[$v['Database']])) $sizes[$v['Database']] = 0;
			$objects[$k]['size'] = $sizes[$v['Database']];
			$objects[$k]['escaped'] = str_replace(
				array('\\','_','%'), array('\\\\','\_','\%'), $v['Database']
			);
		}
			

		// Filter out built-in databases
		$objects = array_merge(array_filter($objects, function($o) {
			return ($o['Database'] != 'information_schema' && $o['Database'] != 'mysql');
		}));


		// Filter result
		return $this->applyFilter($objects, $start, $count, $sortField, $sortDir);


	}

	/**
	 * 
	 * Return a list of MySQL users
	 * @returns array list of users
	 */
	public function getUsers() {

		$this->_validate();

		$users = $this->query("select User as user, Host as host from mysql.user");

		// Filter out built-in users and reindex
		$users = array_merge($this->_filterSystemUsers($users,'user'));

		foreach($users as $k=>$v) {
			$users[$k]['uuid'] = md5($v['user'].$v['host']);
			$users[$k]['description'] = "'{$v['user']}'@'{$v['host']}'";
		}
		return $users;

	}

	/**
	 * 
	 * Return a list of global and optionally database specific privileges -
	 * 	user @ host combinations
	 * @param unknown_type $start
	 * @param unknown_type $count
	 * @param unknown_type $sortField
	 * @param unknown_type $sortDir
	 * @param unknown_type $database
	 * @returns array list of privileges - user @ host combinations
	 */
	public function getPrivileges($start, $count, $sortField, $sortDir, $database=null) {

		$this->_validate(__METHOD__, func_get_args());

		$users = $this->query("select * from mysql.user");

		// Use wildcards
		if($database == '%') {

			$users = $this->query("select * from mysql.db where
				DB like '%\%%' or Db like '%\_%';");
			
		} else {

			$users = $this->query("select * from mysql.user");
			
			// DB specific
			if($database) {
            	$users = array_merge($users,$this->query(sprintf("select * from mysql.db where
                	'%s' like Db or Db = '%s'", $this->escape($database),
	                $this->db_escape($this->escape($database)))));
			}

		}
		
		// Filter out built-in users and reindex
		$users = array_merge($this->_filterSystemUsers($users));
	
		// Create array
		$objects = array();
		foreach($users as $k=>$u) {
	
			$privs = array();
			
			if(empty($u['Db'])) $grants =& self::$global_grants;
			else $grants =& self::$db_grants;
	
			foreach($grants as $g) {
				if($u[$g[0]] == 'Y') $privs[] = $g[1];
			}
			
			if(!count($privs)) $privs[] = 'USAGE';
			elseif(count($privs) == count($grants)) $privs = array('ALL PRIVILEGES');
			$id = md5($u['User'].$u['Host'].(!empty($u['Db']) ? $u['Db'] : ''));
	
			// Global privilege
			if(empty($u['Db'])) {
	
				/* Skip if no privileges are selected and we're looking
				 * for DB privileges
				 */
				if($database && $privs[0] == 'USAGE') continue;
			
				$objects[] = array(
					'uuid' => $id,
					'user' => $u['User'],
					'host' => $u['Host'],
					'type' => 'Global',
					'db' => '',
					'password' => (strlen($u['Password'])),
					'grant' => ($u['Grant_priv'] == 'Y' ? 'Yes' : 'No'),
					'privileges' => implode(', ',$privs)
				);
	
			// Database specific or wildcard privilege
			} else {

				// If we're doing wildcard privileges, make sure
				// Db contains a valid wildcard
				if($database == '%' && !preg_match('/(?<!\\\)[_|%]/', $u['Db']))
					continue;	
	
				$objects[] = array(
					'uuid' => $id,
					'user' => $u['User'],
					'host' => $u['Host'],
					'db' => $u['Db'],
					'type' => ($u['Db'] == $this->db_escape($database) ?
							'Database Specific' :
							'Database wildcard match \''. $u['Db'] .'\''),
					'grant' => ($u['Grant_priv'] == 'Y' ? 'Yes' : 'No'),
					'privileges' => implode(', ', $privs)
				);
			}
		}

		// Filter result
		return $this->applyFilter($objects, $start, $count, $sortField, $sortDir);
		
	}
	

	/**
	 * 
	 * Return a global or database specific priviliege entry - user @ host combination
	 * @param string $user user name of privilege entry
	 * @param string $host host of privilege entry
	 * @param string $database optional Db of privilege entry
	 */
	public function getPrivilege($user, $host, $database='', $wildcard=false) {

		$this->_validate(__METHOD__, func_get_args());
	
		$user = $this->escape($user);
		$host = $this->escape($host);
	
		// Database specific - includes wildcards
		if($database) {
	
			$database = $this->escape($database);

			if(!$wildcard) $database = $this->db_escape($database);

			list($user) = $this->query("select * from mysql.db where Db = '${database}'
					and User = '{$user}' and host = '{$host}';");

			$privs =& self::$db_grants;
		
		// Global
		} else {
			
			list($user) = $this->query("select * from mysql.user where User = '{$user}'
						and Host = '{$host}'");
			
			$privs =& self::$global_grants;
		}
	
		// Create array
		$object = array();
		foreach($privs as $g) {
			if($user[$g[0]] == 'Y') $object['PRIV_'.$g[1]] = 1;
		}
		$object['username'] = $user['User'];
		$object['host'] = $user['Host'];
		
		// Grant privilege
		$object['PRIV_GRANT'] = intval($user['Grant_priv'] == 'Y');
		
		return $object;
	
	}
	
	/**
	 * 
	 * Remove a privilege entry - user @ host combination
	 * @param string $name user name of entry
	 * @param string $host host of entry
	 * @param boolean $dbspecific true if removing a database specific entry
	 * @param string $db name of database of database specific entry
	 * @throws Exception
	 */
	public function removePrivilege($name, $host, $dbspecific, $db=null, $wildcard=0) {
	
		$this->_validate();

		// Remove database specific privilege
		if($dbspecific) {
	
			if(!$db) {
				throw new Exception("No database specified.");
			}

			$dbspec = sprintf(" ON `%s` . * FROM '%s'@'%s';",
				$this->db_escape($db,intval($wildcard)), $this->escape($name), $this->escape($host));
	
			try {
				$this->query("REVOKE ALL PRIVILEGES" . $dbspec);
				$this->query("REVOKE GRANT OPTION" . $dbspec);
			} catch (Exception $e) {
				// These may not even be defined
			}
	
			return;
		}
	
		$this->query(sprintf("DROP USER '%s'@'%s'",
		$this->escape($name), $this->escape($host)));
	
	}
	
	/**
	 * 
	 * Set (save) privilege
	 * @param array $data method input data
	 * @throws OMVException
	 */
	public function setPrivilege($data) {
	
		// Don't bother validating all these json args. We'll do it in PHP
		$this->_validate();
	
		$this->connect();
	
		/* Correct some things so we don't get exceptions
		 * regarding undefined indexes
		 */
		if(empty($data['host'])) $data['host'] = '%';
		if(empty($data['username'])) $data['username'] = '';
	
		// Used in subsequent queries
		$privStr = "";
	
		// Create new user entry
		if ($data['uuid'] == $GLOBALS['OMV_UUID_UNDEFINED']) {
	
			$privStr = sprintf("'%s'@'%s'",$this->escape($data['username']), $this->escape($data['host']));
	
			// Make sure it's unique
			list($exuser) = $this->query(sprintf("select COUNT(*) as c from mysql.user where ".
					"Host = '%s' and User = '%s'", $this->escape($data['host']),
			$this->escape($data['username'])));
	
			if($exuser['c'] > 0)
				throw new OMVException(OMVErrorMsg::E_CONFIG_OBJECT_UNIQUENESS);
	
			// Create User query
			$q = "CREATE USER {$privStr}";
			if(!empty($data['password']))
			$q .= sprintf(" IDENTIFIED BY '%s'", $this->escape($data['password']));
			$this->query($q);
	
			// Create DB with same name as this user?
			if($data['newUserDB'] == 'grantnew' && $data['username']) {
				$this->query(sprintf("CREATE DATABASE `%s`", $this->db_escape($data['username'])));
				$data['newUserDB'] = 'granton';
				$data['grantdatabase'] = $this->db_escape($data['username']);

			}

			// Grant permissions to database?
			if($data['newUserDB'] == 'granton' && !empty($data['grantdatabase'])) {

				$data['grantdatabase'] = $this->db_escape($data['grantdatabase'], true);

				$this->query("GRANT ALL PRIVILEGES ON `{$data['grantdatabase']}` . * TO {$privStr}");
			}
	
		// Do something with existing user
		} else {
	
			$data['exUser'] = $this->escape($data['exUser']);
			$data['exHost'] = $this->escape($data['exHost']);
	
			$privStr = "'{$data['exUser']}'@'{$data['exHost']}'";
	
		}
	
		// Change password?
		if(!empty($data['newpassword']) || $data['settoblank']) {
			$pwq = ($data['settoblank'] ?  "''" : sprintf("PASSWORD('%s')",$this->escape($data['newpassword'])));
			$this->query("SET PASSWORD FOR {$privStr} = {$pwq}");
		}
	
	
		// Privilege changes / sets
		$this->query("SET AUTOCOMMIT=0;");
		$this->query("START TRANSACTION;");
	
		$dbStr = '*';
		$grantlist =& self::$global_grants;
	
		// Set DB permissions?
		$data['exDb'] = $this->db_escape($data['exDb'], intval(!empty($data['wildcard'])));
		if(!empty($data['exDb'])) {

			// Check for wildcard match
			if($data['exDb'] == '%' && !empty($data['dbmatch'])) {
				$dbStr = "`".$this->db_escape($data['dbmatch'],true)."`";
			} else {
				$dbStr = "`{$data['exDb']}`";
			}
			$grantlist =& self::$db_grants;
		}
	
		try {
			$this->query("REVOKE ALL PRIVILEGES ON {$dbStr} . * FROM {$privStr}");
			$this->query("REVOKE GRANT OPTION ON {$dbStr} . * FROM {$privStr}");
		} catch (Exception $e) {
			// Ignored because these may not exist yet
		}
	
		
		$grants = array();
		foreach($grantlist as $g) {
			if($data['PRIV_'.$g[1]]) $grants[] = $g[1];
		}
	
		if(count($grants)) {
	
			// All privileges?
			if(count($grants) == count($grantlist)) {
				$grantStr = 'ALL PRIVILEGES';
			} else {
				$grantStr = implode(', ', $grants);
			}
	
			if($data['PRIV_GRANT']) $grantOption = ' WITH GRANT OPTION';
			else $grantOption = '';
	
			$this->query("GRANT {$grantStr} ON {$dbStr} . * TO {$privStr}{$grantOption};");
		}
	
		$this->query("COMMIT;");
		$this->query("SET AUTOCOMMIT=1;");
	
	}

	/**
	 * Return a list of data volumes where MySQL data can be placed
	 * @return array an array of volumes
	 */
	public function getSQLVolCandidates() {
	
		$this->_validate();

        $objects = $this->configGetList("//system/fstab/mntent[not(contains(opts,'bind'))]");

        // Get the filesystem details for each mount point.
        $result = array();

        // Get default data dir
        exec("sudo grep '^datadir\s' /etc/mysql/my.cnf /etc/mysql/conf.d/* ".
            "| grep -v openmediavault-mysql.cnf | awk '{ print \$NF }' | tail -1", $myconf);

        $result[] = array(
			"uuid" => "none",
			"description" => "Default",
			"datadir" => $myconf[0],
			"datavol" => 0
		);

        foreach ($objects as $objectk => $objectv) {
            $fs = new OMVFilesystem($objectv['fsname']);
            if (!$fs->exists()) {
                continue;
            }
            // Skip the filesystems that are not mounted at the moment.
            if (FALSE === $fs->isMounted())
                continue;

			// No directory
			if(empty($objectv['dir'])) continue;

			// Is the "default" setting part of a data volume?
			if(strpos($result[0]['datadir'], $objectv['dir']) === 0)
				$result[0]['datavol'] = 1;

            // Get the filesystem details.
            $object = array(
                "uuid" => $objectv['uuid'],
                "devicefile" => $fs->getDeviceFile(),
				"datadir" => $objectv['dir'].'/mysql-data',
				"datavol" => 1,
                "description" => $fs->getDeviceFile() // Default value
            );
            // Get some more filesystem details if possible.
            if (FALSE !== ($fsStats = $fs->getStatistics())) {
                $label = $fs->getLabel();
                $object['description'] = sprintf(
                  gettext("%s (%s available)"), !empty($label) ?
                  $label : $object['devicefile'],
                  binary_format($fsStats['available']));
            }
            $result[] = $object;
		}

		return $result;
	}

	/**
	 * Get the default MySQL configuration settings
	 * @return array associative array of settings
	 */
	private function _getConfig() {

		exec('sudo /usr/sbin/mysqld --print-defaults | tail -1 | tr " " "\n" | sed -e \'s/^--//\'',
			$out);

		$defaults = array();
		foreach($out as $o) {
			if(!$o) continue;
			$set = explode('=',$o);
			if(count($set) == 1) $set[1] = true;
			$defaults[$set[0]] = $set[1];
		}

		return $defaults;
	}
	
	/**
	 * 
	 * Filter system users find in self::$system_users from input array
	 * @param array $list list of users
	 * @param string $key array key containing user name
	 */
	private function _filterSystemUsers(&$list, $key='User') {
	
		$system_users = self::$system_users;
		return array_filter($list, function($u) use ($key, $system_users) {
			return (!in_array($u[$key], $system_users));
		});
	
	}

}
